https://disk.yandex.ru/i/cqlx0RbWTQW6-Q - ссылка на презентацию

Финальный проект

Ритейл: Анализ результатов внедрения программы лояльности в сети строительных магазинов "Мы строили, строили и наконец построили"

Описание проекта

В сети строительных магазинов "Мы строили, строили и наконец построили" 3 месяца назад была введена программа лояльности, в нескольких магазинах клиенты могли приобрести карту лояльности по цене 200р и воспользоваться всеми скидками, бонусами и акциями которые предусматривала эта программа лояльности. С точки зрения собственников сети строительных магазинов - данных вполне достаточно чтобы оценить результаты введения программы лояльности и решить: продолжать, приостановить или ее каким-то образом скорректировать. В нашем распоряжении 2 датасета:
retail_dataset.csv Это данные с касс сети магазинов и product_codes.csv данные соответствия ID товара - цене за его единицу

Описание данных

Файл moscow_places.csv:

Как работает программа лояльности на мой взгляд - клиент может получать скидки (товар по более низкой цене), дополнительный товар или услугу бесплатно. При этом клиент частично компенсирует затраты на программу лояльности покупая карту лояльности за 200р. Вполне вероятно что клиент может по этой программе выбирать товар дороже и больше за те же деньги, что он бы потратил и раньше.

План работы

1. Предобработка
1.1 Загрузим необходимые библиотеки
1.2 Выгрузим данные из файлов csv
1.3 выведем информацию о таблице, описание таблицы, уникальные значения столбцов, количество уникальных значений
1.4 проверим на пропуски, изменим форматы данных, переименуем столбцы для удобства
1.5 Исследуем столбцы в таблице date
1.6 Исследуем столбцы в таблице price
2. Исследовательский анализ
2.1. Столбец purchaseId — id (идентификатор покупки)
2.2 Столбец purchasedate дата покупки
2.3 Quantity — количество товара
2.4 CustomerID — id покупателя
2.5 Столбец loyalty_program
2.6 price_per_one - формируем итоговую таблицу с ценами
2.7. Соединим эту таблицу с исходной таблицей трансакций и посчитаем усредненный средний чек с программой лояльности и без по магазинам
3. Анализ программы лояльности
3.1 Распределение количества чеков, количества товара по дням наблюдений, графики
3.2 Распределение количества покупателей и количества покупок на человека по дням наблюдений в зависимости от наличия программы лояльности
3.3 Распределение количества товара на покупателя и размера корзины на человека по дням наблюдений в зависимости от наличия программы лояльности
3.4 Распределение количества проданного товара по магазинам в зависимости от наличия программы лояльности. Столбчатая диаграмма
3.5 График зависимости количества проданного товара на покупателя по магазинам
3.6 График зависимости количества чеков на покупателя по магазинам
3.7 График зависимости количества товаров в чеке на покупателя по магазинам
3.8 Формирование сводной исходной таблицы расчеты CAC без маркетинга
4 Когортный анализ
4.1 формирование исходной таблицы
4.2 создание таблицы профилей
4.3 расчет Retention Rate таблицы удержания клиентов
4.4 тепловая карта Retention Rate таблицы удержания клиентов
4.5 расчет накопленной выручки LTV, построение графика lTV и LTV в динамике
4.6 ROI
4.7 График истории изменений LTV
4.8 График Динамика удержания 7-го и 14-го дней жизни
4.9 Средний и медианные чеки
4.10 Retention rate, возьмем когорты не дневные, а недельные и также посмотрим период в 8 недель
4.10.1 Retention rate для всей таблицы
4.10.2 Retention rate для лояльных клиентов
4.10.3 Retention rate для не лояльных клиентов
5 Формулируем и проверим статистические гипотезы
5.1 Гипотеза о среднем чеке
5.2 Гипотеза о среднем чеке в магазине Shop 0
5.3 Retention, количество покупок на человека, количество товаров на покупателя или в корзине - статистические гипотезы
5.4 Вывод по статистическому анализу гипотез
6 Результаты исследования, выводы и рекомендации для заказчика
7 Рекомендации и материалы для отдела маркетинга и коммерческого департамента

1. Предобработка

К плану работ

1.1 Загрузим необходимые библиотеки

1.2 Выгрузим данные из файлов csv

К плану работ

1.3 выведем информацию о таблице, описание таблицы, уникальные значения столбцов, количество уникальных значений

К плану работ

1.4 проверим на пропуски, изменим форматы данных, переименуем столбцы для удобства

К плану работ

1.5 Исследуем столбцы в таблице date

К плану работ

Удалив 1033 (менее 1%) явных дубликата в таблице мы избавились от 62 пропусков в столбце customer_id

1.6 Исследуем столбцы в таблице price

К плану работ

Количество уникальных товаров в таблицах data и price одинаково

Выведя верх таблицы price мы видим, что однму товару может соответствовать несколько вариантов цен.
Это означает что соединение двух таблиц по идентификатору товара - невозможно.

Вывод по разделу Предобработка:

В таблице data, сформированной из файла retail_dataset.csv 105335 строк и 7 столбцов. Форматы столбцов: float64(2), int64(1), object(4). Все столбы полные без пропусков кроме 'CustomerID'.
Столбец даты совершения покупки 'purchasedate' имеет строчный формат вместо формата даты и времени.
Столбец 'Quantity' имеет большой разброс значений от -74216 до 74214 - скорее всего это ошибка.
Для удобства переименовываем столбцы таблицы data в соотвествии с общепринятыми правилами - строчные буквы, пробелы заменены на нижнее подчеркивание, id (идентификатор) отделен от названия нижним подчеркиванием.
Таблица price сформированная из файла product_codes.csv имеет 2 столбца и 9969 строк, пропусков нет. Переименовываем столбецы в змеином регистре.
Количество уникальных товаров в таблице price - 3159. Минимальная цена 0, максимальная 16888 рублей. Средняя цена 19.5, медианная цена 2.55 рублей, следовательно в таблице price столбце 'product_id' много выбросов.
Стобцы таблицы data:
'item_id'(идентификатор товара):
количество уникальных товаров в таблице data - 3159, соответствует количеству уникальных товаров в таблице price.
'quantity'(количество товара):
количество уникальных значений количества товаров в строке таблицы data - 301, причем в столбце есть как положительные значения, так и отрицательные.
'purchase_date'(дата и время совершения покупки):
приводим столбец 'purchase_date' к временному формату, добавляем столбец 'purchase_day' - дата чека(трансакции)
старт наблюдений 2016-12-01 08:26:00
конец наблюдений 2017-02-28 17:04:00
длительность наблюдений 89 дней
старт программы лояльности 2016-12-01 08:26:00 (совпадает с началом исследования).
'customer_id':
количество уникальных покупателей в таблице data - 1750
количество уникальных покупателей в таблице data, участвующих в программе лояльности - 587,
количество уникальных покупателей в таблице data, не участвующих в программе лояльности - 1163. Покупателей участвующих в программе лояльности почти в 2 раза меньше чем обычных покупателей.
'shop_id':
Всего магазинов в сети - 31.
'loyalty_program:
В столбце всего 2 уникальных значения 0.0 и 1.0. Количество строк в таблице с программой лояльности и без - 105335. Количество строк в таблице с программой лояльности - 23842, количество строк в таблице без программы лояльности - 81493.
Проверив таблицу data на явные дубликаты мы обнаружили 1033 (менее 1%) явных дубликата, удалив их мы одновременно избавились от 62 пропусков в столбце customer_id.
Явных дубликатов в таблице price нет.
Количество вариантов цен в таблице price - 586, причем одному идентификатору товара может соответствовать несколько вариантов цен.

2 Исследовательский анализ

К плану работ

Изучим следующие параметры датасетов:

2.1 Столбец purchaseId — id (идентификатор покупки)

Определм соотношение количества позиций покупок с программой лояльности и без в магазинах где она действует - по каждому магазину и по всем где она есть.

Вывод:

Из анализа мы видим, что в среднем доля количества позиций в чеках по программе лояльности по всем магазинам - 22,45%. Отсортировав данные по магазинам и по участию в программе лояльности и просуммировав количества позиций в чеках мы получили результат: максимальное количество позиций в чеках в наблюдаемый период были выбиты в магазине Shop 0. Соотношение количества позиций в чеках по программе лояльности к общей сумме количества позиций в чеках в нем выше чем вцелом по всем остальным магазинам и составляет 31,83%. То есть у программы лояльности есть потенциал роста ассортимента приобретаемого товара.

2.2 Столбец purchasedate дата покупки

К плану работ
Вычислим первую и последнюю даты покупки

Вывод:

Всего программма лояльности задействована в 4-х магазинах.
В трех из этих магазинов программа лояльности стартовала с момента открытия магазина(дата первого чека). Магазин 8 - программа лояльности стартовала через 6 дней от момента первого чека.
Последняя дата трансакций в лояльных магазинах "дожила" до окончания исследования лишь в магазине 0.
Магазин 28 первая и последняя дата и время покупки совпадает - скорее всего 30 чеков в одно и то же время - это ошибка.
Даты перых чеков в магазинах отстоят от даты начала исследования дальше с возрастанием номера магазина. Логично предположить что магазины открывались последовательно один за другим. Примечательно что в магазине 19 последний чек был пробит за месяц до конца исследования как с программой лояльности так и без нее. Значит магазин месяц не работал.

2.3 Столбец Quantity количество товара

К плану работ

Вывод:

Мы видим в столбце 'quantity' большие выбросы в обе стороны. Вариантов количества товара - 301, не так уж и много. Максимальное количесво товара по программе лояльности 3905, без программы лояльности 74216. Минимальное количество товара по программе лояльности минус 241, без программы - минус 74216. Сумма проданного товара всего, если оставить отрицательные количества товара 822213, если количество товара сделать все положительным, то 1061321. Таким образом, почти 78000 единиц товара, а это 22,5% "теряются" из-за отрицательных количества. Значит оставлять так как есть - нельзя, меняем знак количества товара.

Три строки снизу похожи на ошибки: 3-я и 4-я строки отличаются только минутами пробития чека и обозначением чека. 2-я и 3-я строки имеют похожий идентификатор чека - судя по всему кассовые аппараты с чеками C54... и С53... выдавали ошибки и печатали ошибочное количество товаров со знаком минус.

Количество товара купленного с программой лояльности стало больше: разброс между первым и 3-м квартилем больше и медиана выше, а выбросы стали реже.

Если мы изменяем минус в количестве товара на плюс, то в программе лояльности первый квартиль уже не 0.

Вывод:

Распределение количества товаров в чеке между магазинами показывает что магазин 19 имеет самое высокое медианное число купленных товаров. И в этом магазине практически нет выбросов.
Как мы уже раньше исследовали - в этом магазине чеков по программе лояльноти почти в 2 раза больше чем без нее.
Скорее всего маркетологам надо присмотреться к Shop 19 - возможно в нем созданы условия для покупок большого количества товаров и это дает результат. Возникает вопрос - почему магазин 19 месяц не работал? Вполне возможно что программа лояльности в нем дала неожиданный результат, ведь количество товара, купленного по акции может свидетельствовать о потерях магазина.
В магазине Shop 0 - в котором пробивается более 90% чеков медианнное число купленных товаров низкое и много выбросов. Скорее всего этот магазин имеет очень большой ассортимент товаров и находится в проходном месте, что способствует притоку посетителей, однако их нельзя назвать оптовыми - скорее всего в него заходят за всякой мелочевкой. Кандидатами на запрет внедрения программы лояльности могли бы стать магазины Shop 3, Shop 14, Shop 2, Shop 2, Shop 22. У них высокие медианные значения количества товара(смотри на магазин 19).
Аутсайдерами оптовых продаж являются магазины 11, 5, 27, 20, 25, 21, 9, 29, 27, 15, 30 - в них низкое количество проданного товара и нет выбросов. Именно эти магазины и рекомендуется подключать к программе лояльности для стимулирования продаж.

отбросим 1% значений установив уровень отсечения количества товаров на уровне 100.

Вывод:

В магазине Shop 8 программа лояльности не приносит ожидаемого результата медианное количество товара без программы лояльности выше чем с ней. Похоже покупатели в этом магазине получают скидки и подарки и не считают нужным купить больше.
Также нужно обратить внимание на Shop 23 - у него высокое медианное количество покупок, при условии всего 2-х чеков за все время наблюдений, если он открыт недавно, и чеков будет больше - то это еще один кандидат на исключение из программы лояльности.

Увы, но магазин открыт давно и 2 крупных чека - не показатель. Есть вероятность что этот магазин открыт рядом с новостройками в которых делают ремонт или даже что-то строят, потому что крупные заказы делают строители и отделочники.

2.4 Столбец CustomerID - id покупателя

К плану работ
Решаем в каких случаях стоит отбросить данные с пропусками в этом столбце (построим ящики с усами по магазинам и определим выбросы, посчитаем количество покупок после удаления выбросов, решим стоит ли это делать Уберем редкие и выбивающиеся значения. Опишем, какие особенности обнаружили. Общий результат удаления решим что делать с отрицательным количеством товара)

Вывод:

Столбец "customer_id" имеет 36148 пропусков, это более трети данных в таблице. Удалять их не нужно, но мы будем отсекать строки с пустыми значениями в этом столбце когда будем рассчитывать значения на уникального пользователя.
Пустое поле "customer_id" - скорее всего это оплата за наличный расчет, таким образом невозможно определить сколт=ько всего уникальных посетителей реально было в магазинах сети. Это могло быть и 1 человек и 100.
На диаграмме "ящик с усами" распределения уникальных покупателей по дням наблюдений мы видим, что по программе лояльности ящики присутствуют дискретно в 5-ти днях наблюдений, в то время как без программы лояльности боксов больше. причем в те дни где есть боксы по программе лояльности количество уникальных покупателей без программы лояльности ниже чем с ней. Это всё может свидетельствовать о разовых акциях по программе лояльности в эти дни, на которые откликаются владельцы карт лояльности и поэтому покупателей по программе лояльности становится больше чем обычных покупателей. Таким образом можно сделать вывод, что маркетинговая управляемость лояльных клиентов находится на высоом уровне.
Однако львиная доля лояльных клиентов посещает Shop 0 и из-за большой проходимости - медиана количества лояльных пользователей во все дни наблюдений ниже чем не лояльных.

2.5 Столбец loyalty_program

К плану работ

участвует ли покупатель в программе лояльности - определим как распределяется количество покупателей, чеков и товаров по программе лояльности и без в отсортированных магазинах;

Количество лояльныых клиентов 22,4%
Сформируем таблицу обещего датасета data_purchase_date_1 и таблицу для анализа программы лояльности data_purchase_date_2

Вывод:

В графиках наблюдается сезонность. Есть явные "зубья" - недели наблюдений - в понедельник минимум - в воскресенье максимум. Виден разрыв на новогодние праздники с 23 декабря по 4 января. На графике количества чеков на человека в день есть явные пики 21 января и 28 февраля - это скорее всего связано с днями выдачи заработной платы.
На графике количества товара на покупателя есть 2 явных пика вниз - 9 декабря и 11 февраля - это скорее всего связано с отсутствием денег перед авансом. В январе это не наблюдается потому что - каникулы и люди тратят подарки на новый год. визуально средняя линия количества товара в день в декабре, январе и феврале не меняется.
Последний график 'Суммарное количество товара в чеке на покупателя в день наблюдений' показывает общую тенденцию для розничной торговли в это время года - всплеск продаж перед новым годом, "голодный" январь и небольшой рост в феврале.
В общем датасете разброс от 1 покупки в день до 2,2, у лояльных - от 1 до 1,8, у обычных клиентов от 1 до 2,6. Обычные клиенты совершают покупки чаще.
Суммарное количество тоара проданное в день на покупателя - от 79 до 306 в общем датасете, у лояльных - от 24 до 367, у обычных от 57 до 403. Обычные клиенты покупают товаров в день больше.
Товаров в корзине на клиента в день в общем датасете - от 0,6 до 13,4, у лояльных - от 1,5 до 51,1, у обычных клиентов - 0,96 до 16,5. У лояльных клиентов в среднем корзина товаров "богаче".
Количество клиентов в день в общем датасете от 11 до 119, лояльных - от 3 до 43, обычных - от 7 до 79. Обычных клиентов больше почти в 2 раза, но если учесть что соотношение лояльных к обычным почти 1:4, то получится что лояльные ходят в 2 раза чаще.

2.6 price_per_one - формируем итоговую таблицу с ценами

К плану работ

price_per_one - формируем итоговую таблицу с ценами для использования в дальнейшем цена товара, выделяем 2 цены: цену по программе лояльности - минимальную, без программы лояльности медианную. Формируем и добавляем столбец 'price'в таблице product_codes.csv в соответствии с этим правилом;

Формируем прайс-лист для программы лояльности. Делаем предположение что
без программы лояльности позиции присваивается медианная цена, по программе лояльности действует минимальная цена.

Вывод

В прайсе есть 382 товара по нулевой стоимости - скорее всего это второй товар бесплатно, товары как бонусы и акционные товары впридачу.Также в таблице присутствуют 665 товаров которые не меняют цену вне зависимости действует программа лояльности или нет.

2.7 Соединим эту таблицу с исходной таблицей трансакций и посчитаем усредненный средний чек с программой лояльности и без по магазинам

К плану работ

Вывод по разделу Исследовательский Анализ

В флагманском магазине Shop 0 по программе лояльноти приобретается более широкий ассортимент чем во всей сети. Поскольку на этот магазин приходится 92% чеков, то следовательно введя программу лояльности повсеместно можно расширить номенклатуру часто продаваемого товара.

На сегодняшний день программа лояльности действует в 4-х магазинах из 31. Один из магазинов(Shop 19) в программе лояльности месяц не работал.

Даты первых чеков в магазинах отстоят от даты начала исследования дальше с возрастанием номера магазина. Логично предположить что магазины открывались последовательно один за другим. Примечательно что в магазине 19 последний чек был пробит за месяц до конца исследования как с программой лояльности так и без нее. Значит магазин месяц не работал. По странному стечению обстоятельств внем было больше всего продано товаров по программе лояльности - возможно после того как закончились товары по программе лояльности клиенты перестали там покупать или владельцы магазина были недовольны результатами программы.
Из-за отрицательных количеств товара мы теряем почти 78000 единиц товара, а это 22,5% от общего количества товара. Поэтому мы взяли количество товаров по модулю и отсекли выбросы на уровне 100 шт, отбросив таком образом 1% данных.

Распределение количества товаров в чеке между магазинами показывает что магазин 19 имеет самое высокое медианное число купленных товаров. И в этом магазине практически нет выбросов.
Как мы уже раньше исследовали - в этом магазине чеков по программе лояльноти почти в 2 раза больше чем без нее.
Скорее всего маркетологам надо присмотреться к Shop 19 - возможно в нем созданы условия для покупок большого количества товаров и это дает результат, также вполне возможно что программа лояльности в нем дала неожиданный результат, ведь количество товара, купленного по акции может свидетельствовать и о потерях магазина.
В магазине Shop 0 - в котором пробивается более 90% чеков медианнное число купленных товаров низкое и много выбросов. Скорее всего этот магазин имеет очень большой ассортимент товаров и находится в проходном месте, что способствует притоку посетителей, однако их нельзя назвать оптовыми - скорее всего в него заходят за всякой мелочевкой. Кандидатами на запрет внедрения программы лояльности могли бы стать магазины Shop 3, Shop 14, Shop 2, Shop 2, Shop 22. У них высокие медианные значения количества товара(учитываем опыт магазина 19).
Аутсайдерами оптовых продаж являются магазины 11, 5, 27, 20, 25, 21, 9, 29, 27, 15, 30 - в них низкое количество проданного товара и нет выбросов. Именно эти магазины и рекомендуется подключать к программе лояльности для стимулирования продаж.

В магазине Shop 8 программа лояльности не приносит ожидаемого результата медианное количество товара без программы лояльности выше чем с ней. Похоже покупатели в этом магазине получают скидки и подарки и не считают нужным купить больше.
Столбец "customer_id" имеет много пропусков, это более трети данных в таблице. Решено не удалять, но стоит отсекать строки с пустыми значениями в этом столбце когда будем рассчитывать значения на уникального пользователя.

В графиках количества чеков, товара и покупателей на день наблюдений наблюдается сезонность. Есть явные "зубья" - недели наблюдений - в понедельник минимум - в воскресенье максимум. Виден разрыв на новогодние праздники с 23 декабря по 4 января. На графике количества чеков на человека в день есть явные пики 21 января и 28 февраля - это скорее всего связано с днями выдачи заработной платы.
На графике количества товара на покупателя есть 2 явных пика вниз - 9 декабря и 11 февраля - это скорее всего связано с отсутствием денег перед авансом. В январе это не наблюдается потому что - каникулы и люди тратят подарки на новый год. визуально средняя линия количества товара в день в декабре, январе и феврале не меняется.
Последний график 'Суммарное чеков * количество товара на покупателей в день наблюдений' показывает общую тенденцию для розничной торговли в это время года - всплеск продаж перед новым годом, "голодный" январь и небольшой рост в феврале.

Судя по среднему чеку оптовыми магазинами являются 24,25,26 и 27, возможно еще 3-ий. Судя по средней цене позиции в чеке - когда разница в чеке по программе лояльности и без большая - 8-ой магазин - то клиент покупает очень мало по программе лояльности. Если цена позиции в чеке одинакова - магазин 19, то клиент покупает слишком много и это магазину не выгодно. В магазине 0 цена товара в чеке по программе лояльности ниже в 2 с небольшим раза - это похоже оптимальный вариант.

3 Анализ программы лояльности

К плану работ

Тут удалять строки с пустыми идентификаторам покупателей не нужно

3.1 Распределение количества чеков, количества товара по дням наблюдений, графики

К плану работ

3.2 Распределение количества покупателей и количества покупок на человека по дням наблюдений в зависимости от наличия программы лояльности

К плану работ

Вывод:

У обычных клиентов количество чеков и товара по дням наблюдений больше. Коичество покупателей и покупок на человека тоже выше у клиентов без программы лояльности.

3.3 Распределение количества товара на покупателя и размера корзины на человека по дням наблюдений в зависимости от наличия программы лояльности

К плану работ

Вывод

Красная линия количество чеков без программы лояльности имеет более выраженный пилообразный характер в зависимости от дня недели, голубая линия количества чеков имеет более сглаженный характер. Скорее всего это связано с тем, что клиенты участвующие в программе лояльности это профессионалы строительства и поход в строительный магазин у них не зависит от дня недели. Клиенты без программы лояльности - это обычные люди - в рабочие дни они на работе и в строительные магазины не ходят.
Количество проданного товара без программы лояльности в декабре и начале января - скорее всего связаны с новогодними подарками - до нового года покупаются подарки родственникам и друзьям, после нового года - тратятся подаренные деньги.
Интересны всплески 1 февраля и 22 февраля на голубой линии количества товара, проданного по программе лояльности. Это свидетельствует о покупках к мужчинам подарков в строительном магазине к 23 февраля, скорее всего лояльные клиенты действительно работают в строительстве и подарки дарят из строительного магазина (например инструмент).

3.4 Распределение количества проданного товара по магазинам в зависимости от наличия программы лояльности. Столбчатая диаграмма

К плану работ

Вывод

Максимальное количество товара продается в Shop 0 - количество товара проданного в других магазинах на уровне статистической пограешности кроме магазинов 1, 3, 4 и 6.
Причем по программе лояльности продано в 2,3 раза меньше.

3.5 График зависимости количества проданного товара на покупателя по магазинам

К плану работ

Вывод

В пятерку лидеров по количеству проданного товара на покупателя входят: Shop 3, Shop 6, Shop 14, Shop 5 и Shop 26.
Тройка аутсайдеров по количеству товара Shop 20, Shop 21, Shop 29.
В программе лояльности места по количеству проданного товара на покупателя распределены Shop 19, Shop 28, Shop 0 и Shop 8. В Первых двух по программе лояльности продано товара больше чем без программы лояльности.
Тут надо учитывать что мы удалили пропуски в данных о покупателях, поэтому товара без программы лояльности реально больше.

3.6 График зависимости количества чеков на покупателя по магазинам

К плану работ

Вывод

Явным лидером по количеству чеков на человека является Shop 6 (в 6 раз больше чем всреднем по магазинам), далее следуют Shop 13 и Shop 3. В Shop 8 и Shop 19 чеков по программе лояльности больше чем без нее. В Shop 28 их одинаковое количество, а в Shop 0 меньше. Однако если учесnь удаление пропусков, то вполне вероятно что покупатели были "с улицы" и в таблицу попали как NaN

3.7 График зависимости количества товаров в чеке на покупателя по магазинам

К плану работ

Вывод

В четверку лидеров по количеству проданного товара в чеке входят: Shop 3, Shop 26, Shop 14 и Shop 5.
Тройка аутсайдеров по количеству товара в чеке Shop 29, Shop 20, Shop 21.
В программе лояльности места по количеству проданного товара в чеке распределены Shop 28, Shop 19, Shop 0 и Shop 8. В Первых трех по программе лояльности продано товара больше чем без программы лояльности.

3.8 Формирование сводной исходной таблицы расчеты CAC без маркетинга

К плану работ

Вывод

Для когортного анализа мы сформировали итоговую таблицу с суммами чеков, которая равна произведению положительного количества товара, умноженная на цену в зависимости от наличия программы лояльности. Суммы стоимости товаров в чеке объединены по идентификатору чека purchase_id.
Для расчета стоимости привлечения клиентов мы рассчитываем CAC. Расчет производится следующим образом: все чеки по программе лояльности пересчитываются по цене без программы лояльности, разница сумм этих чеков - это потери от введения программы лояльности. Кроме того в затраты по программе лояльности добавляется сумма всех чеков товаров с количеством равным 0 и идентификатором участия в программе лояльности (логика в том что этот товар добавлен в чек в размере 1 штука). Сумму затрат мы делим на количество уникальных покупателей по программе лояльности. Итого САС без учета прибыли от продажи карт лояльности(каждая 200р) и затрат на маркетинг связанный с этой акцией 499,48 рублей на человека.
Каждый участник акции минимум 1 раз купил карту лояльности за 200 р и на маркетинг акции были затраты: зп маркетолога и выпуск пластиковых карт (как минимум), эти данные мы учтем ниже.

Вывод по Анализу программы лояльности

График количества чеков без программы лояльности вцелом выше чем с ней, амплитуда шире. Лояльные клиенты покупают меньше. Однако ярко выраженный пилообразный характер в зависимости от дня недели у лояльных клиентов отсутствует, можно сделать вывод что это клиенты-профессиналы и они посещают нашу сеть как в выходные, так и в будние дни, естественно их много меньше чем обычных клиентов. Вал продажи товара по дням в январе и декабре связаны с сезоном распродаж и каникулами. В феврале у клиентов программы лояльности есть всплески 1 и 22 февраля - скорее всего это подарки себе или друзьям мужчина - а что профессионал может подарить коллегам или друзьям - то что в доме пригодится настоящему мужчине и это как раз ассортимент нашей сети.
Сеть на данный момент имеет расширение магазинов, но продажи хорошо идут лишь в одном магазине Shop 0. В нем товаров по программе лояльности продается в 2,5 раза меньше чем без нее.
В пятерку лидеров по количеству проданного товара на покупателя входят: Shop 3, Shop 6, Shop 14, Shop 5 и Shop 26.
Тройка аутсайдеров по количеству товара Shop 20, Shop 21, Shop 29.
В программе лояльности места по количеству проданного товара на покупателя распределены между Shop 19, Shop 28, Shop 0 и Shop 8. В Первых двух по программе лояльности продано товара больше чем без программы лояльности.

Явным лидером по количеству чеков на человека является Shop 6 (в 6 раз больше чем в среднем по магазинам), далее следуют Shop 13 и Shop 3. В Shop 8 и Shop 19 чеков по программе лояльности больше чем без нее. В Shop 28 их одинаковое количество, а в Shop 0 меньше.
В четверкуерку лидеров по количеству проданного товара в чеке входят: Shop 3, Shop 26, Shop 14 и Shop 5.
Тройка аутсайдеров по количеству товара в чеке Shop 29, Shop 20, Shop 21.
В программе лояльности места по количеству проданного товара в чеке распределены Shop 28, Shop 19, Shop 0 и Shop 8. В Первых двух по программе лояльности продано товара больше чем без программы лояльности.
По результатам анализа предположили что в магазинах нашей сети продаются товары по программе лояльности по минимальной цене, а без по медианной. Из таблицы цен мы сыормировали прайс лояльности и обычный и добавили эти цены в зависимости от маркета лояльности на чеке. Далее мы сгруппировали данные по идентификатору чека и посчитали сумму покупок в каждом чеке. Эти данные были использованы в дальнейшем когортном анализе. Кроме того мы посчитали дельту между товарами программы лояльности проданными по медианному и лояльному прайсу и сочли это затратами на рекламу программы лояльности (Низкие цены - это тоже реклама). К этой сумме прибавили все товары отданные бесплатно по программе лояльности и получилось что сеть магазинов потеряла на программе лояльности по 499 рублей на каждого привлеченного покупателя. Это не чистые за траты CAC на привлечение покупателей, данные зп маркетологов и изготовления карт мы добавим в когортном анализе. Так как карты программы лояльности платные, то частично затраты на привлечение покупателя уменьшаются на возврат от продажи карт. И это тоже будет учтено в дальнейшем. Итогом анализа программы лояльности стала таблица с чеками.

4. Когортный анализ

4.1 формирование исходной таблицы

К плану работ

В таблице осталось 4061 чек (строки) и 5 столбцов. Переименовываем столбцы дя удобства когортного анализа и чтобы воспользоваться стандартными функциями рассчета

4.2 Cоздание таблицы профилей

К плану работ

В профилях 1727 пользователя(строк), есть столбец даты первой аокупки, месяца первой покупки и даты и времени первой покупки.

4.3 Расчет Retention Rate таблицы удержания клиентов

К плану работ

Удалив дубликаты получили таблицу с 4025 чеками

Вывод

Максимальное количество привлеченных покупателей было накануне нового года в выходные дни - это и логично, люди выбирали и покупали подарки к новому году. Дальнейшие пики в январе связаны с покупкой на подаренные на новый год деньги, пики в феврале - с подаркми к 23 февраля.
Привлечение максимум покупателей в декабре могут быть связаны с сезонными распродажами во всех розничных магазинах и исследуемая нами сеть "Мы строили, строили и наконец построили" - не исключение.

4.4 тепловая карта Retention Rate таблицы удержания клиентов

К плану работ

Вывод

Плохая динамика удержания приходится на когорту 21,22 и 23 декабря, 16 января, 30 января, 6, 7 и 14 февраля. Когорты за 11 и 18 декабря, 8 и 15 января и 5 и 22 февраля - вообще отсутствуют.
Возможно клиенты пришедшие в магазин в день влюбленных "не наш профиль" и на день защитника отечества приходят только постоянные клиенты, которые знают ассортимент и делают осознанные покупки.

Вывод

всплеск когорт 9 и 10 февраля на 9-ый день можно объяснить покупками накануне 23 февраля(в начале месяца заходили и что-то запланировали купить - 18-19-в день зарплаты купили).

Пилообразная кривая удержания мне ни очем не говорит, разве что о том что визиты в наши магазины совершаются не регулярно.

4.5 расчет накопленной выручки LTV, построение графика lTV и LTV в динамике

К плану работ

есть снижение по месяцам покупок карт лояльности - это связано как с сезонностью, так и, возможно, с разочарованием в самой программе

Вывод

С добавкой от покупки карт лояльности накопленная выручка лояльных клиентов все равно не выше чем у не лояльных

Вывод

Накопленная выручка по программе лояльности ниже не смотря даже на то что карты лояльности продаются ежемесячно, и она растет медленнее чем без программы лояльности. Кроме того есть снижение по месяцам покупок карт лояльности - это связано как с сезонностью, так и, возможно, с разочарованием в самой программе

4.6 ROI

К плану работ
В нашем проекте нет CAC и ROI и посчитать их невозможно. То, как мы это делаем - принципиально не верно. "Недополученная прибыль" не входит в затраты по определению. У нас в прибыли есть два принципиально разные части - выручка и затраты. Уменьшение выручки - это просто уменьшение выручки, к затратам это никак не относится. Выручки не достаточно для расчета САС и ROI. Именно поэтому расчет CAC и ROI будем считать ориентировочным.

Вывод

Ориентировочные затраты на программу лояльности (1006 рублей) и выручка от нее изначально отличаются. Таким образом, затраты не окупились в течении рассматриваемого периода. По программе лояльности накопленная выручка ниже на 23%.

4.7 График истории изменений LTV

К плану работ
в целом в динамике и в количестве покупок видно, что 14 дней для исследования мало. Поэтому возьмем временной период в 60 дней и посмотрим репльно динамику LTV

Вывод

Накопленная выручка сильнее всего увеличилась накануне и после Нового года и перед 23 февраля - причина подарки.

4.8 График Динамика удержания 7-го и 14-го дней жизни

К плану работ

Вывод

Удержание 7-го дня жизни намного выше чем 14-го. Прямая в конце графика - это новогодние каникулы.

4.9 Средний и медианные чеки

К плану работ

Средний чек:
по всей таблице чеков 358 рублей
по программе лояльности 155 рублей
без программы лояльности 455 рубля
по программе лояльности без нулевых покупок 165 рублей
без программы лояльности и без нулевых покупок 462 рубля Медианный чек: по всей таблице чеков 176 рублей
по программе лояльности 91 рублей
без программы лояльности 251 рубля по программе лояльности без нулевых покупок 99 рублей
без программы лояльности и без нулевых покупок 260 рубля

Вывод

Несмотря на всплеск продаж перед новым годом - средний чек в декабре самый маленький - видимо но новый год строительные товары дарят недорогие. А вот после нового года те кому подарили деньги на новый год приходят за дорогими товарами.
Несмотря на мужской праздник в феврале - чек в этом месяце ниже чем в январе, наверное потому что в январе 2 праздника - навый год, рождество и старый новый год, а в феврале только один. И еще как вывод - не только мужчины покупают подарки в сети магазинов "Мы строили, строили и наконец построили", иначе суммарный чек в январе и феврале был бы одинаковым.

Вывод

Количество привлеченных пользователей в день мы уже рассматривали, в Неделю - максимум на 49 неделе - за 3 недели до нового года - сезон распродаж и покупок к новому году, падение на последней неделе года связано с каникулами, далее плавный рост к началу-середине феврала, за ним снижение - праздники кончились, деньги тоже. Количество привлеченных пользователей по месяцам постоянно снижается - это тревожная тенденци - маркетинг работает плохо - нужно что-то менять.

Вывод по разделу когортный анализ

Анализ ведется по таблице с 4061 чеку. Из нее выделено 1727 профиля покупателя. Когорты сформированы по дню первой покупки, в данных всего 89 лайфтаймов На графике динамики привлечения покупателей видно, что максимальное количество привлеченных покупателей было накануне нового года в декабре, в январе и феврале привлеченных новых покупателей в разы меньше.
Динамика удержания в когортах с 21 по 23 декабря, 16 января, 30 января, 6, 7 и 14 февраля - плохая. Когорты за 11 и 18 декабря, 8 и 15 января и 5 и 22 февраля - вообще отсутствуют.
Возможно клиенты пришедшие в магазин в день влюбленных "не наш профиль" и на день защитника отечества приходят только постоянные(не новые) клиенты, которые знают ассортимент и делают осознанные покупки.
Тепловая карта удержания клиентов имеет неоднородный характер, это может свидетельствовать о том, что в строительные магазины заходят крайне редко и нерегулярно. Количество лояльных пользователей уменьшается от месяца к месяцу более чем в 2 раза каждый месяц - это свидетельствует об отказе клиентов оплачивать карту лояльности из-за того что выгода по ней может быть меньше чем затраты, однако также в этом вопросе нельзя исключать фактор сезонности. Есть вероятность того что с началом дачного сезона в апреле-марте покупок станет больше и клиентов программы лояльности тоже. Всего уникальных лояльных клиентов 524, а проданных карт 1543. При рассчетах уникальных лояльных пользователей мы отбросили профили без идентификатора пользователя. Получается что затраты среднего клиента программы лояльности на карты привелегий за три месяца составили 589 рублей.
До учета выручк от карт лояльности накопленная выручка на человека LTV по программе лояльности была ниже чем клиента не участвующего в ней. При учете выручки от карт - лояльные клиенты дают выручку сетинаших магазинов выше. Однако накопленная выручка клиентов программы лояльности растет медленней чем выручка у обычных клиентов. Программа лояльности окупилась почти в 6-7 раз и львную долю окупаемости занимает выручка от карт. Вполне возможно что можено в дальнейшем отказаться от помесячной оплаты карт лояльности или снизить цену карт постепенно. В динамике LTV больше всего накапливалась накануне праздников(подарки) на новый год и день защитника отечества. Средний чек по всей таблице примерно 358 рублей, по программе лояльности на 54% ниже, без программы - на 29% выше. Медианный чек по всей таблице составляет 176 рублей, по программе лояльности на 44% ниже, без программы лояльности на 48% выше. Медианный чек самый высокий в январе - люди сами совершают покупки в период новогодних праздников, есть версия что на подаренные деньги. Январский медианный чек самый низкий - новогодние сувениры как правило недорогие. Февральский чет выше декабрьского но меньше январского. Среднее количество уникальных пользователей за день: 27 Среднее количество уникальных пользователей за неделю: 140 Среднее количество уникальных пользователей за месяц: 514 В день привлекалось больше всего в январе, по неделям - максимум на 49-ю неделю (сезон новогодних распродаж). Помесячно количество лояльных клиентов постоянно сокращается.

4.10 Retention rate, возьмем когорты не дневные, а недельные и также посмотрим период в 8 недель

К плану работ
Из-за сложности понимамия удержания клиентов по дням и динамики этого удержания попробуем укрупнить исследование и взять недельные когорты и интервал 8 недель.

4.10.1 Retention rate для всей таблицы

К плану работ

4.10.2 Retention rate для лояльных клиентов

К плану работ

4.10.3 Retention rate для не лояльных клиентов

К плану работ

Вывод

Удержание клиентов без программы лояльности в целом выглядит лучше, однако у лояльных клиентов есть когорты и лайфтаймы в которых коэффициент 50%, это скорее всего на 23 февраля - половина лояльных клиентов вернулась накануне мужского праздника. Кроме того 4 и 6 лайфтаймы у обоих групп выше в динамике первой недели января чем остальные - это связано с накопленны спросом за праздничные дни. Тепловая карты лояльных клиентов имеют более контрастные ячейки, не лояльных в целом карта теплее.

5. Формулируем и проверим статистические гипотезы.

К плану работ

5.1 Гипотеза о среднем чеке

К плану работ

H0: средний чек участников программы лояльности и у остальных покупателей равны.
H1: средний чек участников программы лояльности и у остальных покупателей не равны.

5.2 Гипотеза о среднем чеке в магазине Shop 0

К плану работ

H0: средний чек в магазине Shop 0 участников программы лояльности и у остальных покупателей равны.
H1: средний чек в магазине Shop 0 участников программы лояльности и у остальных покупателей не равны.

Проверим гипотезы с помощью статистического теста. </h3>

5.3 Retention, количество покупок на человека, количество товаров на покупателя или в корзине - статистические гипотезы

К плану работ

5.4 Вывод по статистическому анализу гипотез

К плану работ

Вывод:

Мы проверили статистические гипотезы о равенстве средних чеков во всех магазинах по программе лояльности и без с помощью теста Манна-Уинни и отвергли нулевую гипотезу. В проверке статистической гипотезы о равестве чеков по программе лояльности и без в головном магазине Shop 0 мы тоже отвергли нулевую гипотезу - чеки не равны.
Проверка статистических гипотез о равенстве удержания клиентов и количества товаров в чеке по программе лояльности и без не подтвердились - они разные

6. Результаты исследования, выводы и рекомендации для заказчика

К плану работ

Исходя из вышеперечисленного подводим итог: Данные исследования включают 3 месяца наблюдений (с 2016-12-01 08:26:00) - данные с касс 31 магазина сети "Мы строили, строили и наконец построили". Из них только один магазин - с большим оборотом товара (92%) - Shop 0. Всего 4061 чек и 1727 профиля покупателя для анализа. В 4-х магазинах действует программа лояльности, количество лояльных клиентов в 2 раза меньше чем не лояльных.
В исходных данных есть пропуски, дубликаты, неверные и отрицательные значения. Помимо этого в таблице товар-цена одному товару соответствует до 7 цен. Однозначной привязки цен из прайса к товарам в таблице данных с касс - нет, ни по времени ни по номеру магазина.
Поэтому в исследовании были сделаны допущения, удалена часть данных и заменены отрицательные значения на положительные. В силу этого наше исследование носит ориентировочный характер и в нем могут быть неточности.
Привязка цен происходит по принципу: минимальная цена соответствует прайсу программы лояльности, медианная - основному. Доля товаров купленных по программе лояльности во всей сети 22%, а в Shop 0 - почти на 10% больше. Значит при повышении оборота магазинов - будет расти и доля покупок по программе лояльности, есть потенциал для роста. Медианная цена прайса 2,55 рублей, средняя - 19 - это свидетельствует о низком прайсе и больших выбросах. В одном из магазинов лояльности самое высокое количество товаров чеке - Shop 19,однако последний месяц этот магазин не работает. Может быть товары закончились (по программе лояльности) или собственники недовольны ходом реализации программы. В магазине 8 в чеках по программе лояльности маленькое количество товаров - или клиенты не хотят пользоваться программой или выбор ассортимента не большой.
магазины 5, 9, 11, 15, 20, 21, 25, 27, 29, 30 - кандидаты на введение программы лояльности в дальнейшем - там мало продается товара. При этом магазин 5 входит в топ 5 магазинов в которых максимум товара на покупателя и по количеству проданного товара в чеке, а 20, 21, 29 - минимальное количество товара на покупателя.
Shop 3, Shop 14, Shop 2, Shop 22 - продается много товара и без программы лояльности, если внедрить там программу лояльности снизится средний и медианный чек. В графиках показателей магазина прослеживается сезонность и зависимость от дней недели. У лояльных клиентов эта зависимость меньше выражена - можно сделать предположение что лояльные клиенты - это профессионалы строительной отрасли и они ходят за покупками не только в праздники и выходные. Также есть всплески в дни праздников и накануне(Новый год и 23 февраля). 14 февраля - спад и когорты купившие первый раз в районе этого дня малоактивны - это не наш праздник. Интересный момент - покупки совершенные до нового года имеют большое число и низкую стоимость, а покупки совершенные в новогодние каникулы имеют больший чек, отсюда вывод - сувениры к новому году покупаются массово и по низкой цене, а после нового года люди идут за дорогими подарками. Можно сделать вывод что программу лояльности нужно ориентировать на профессионалов, и на покупки себе, а не на сувениры друзьям. Кроме того, в графиках прослеживается тенденция немного позже 5 и 20 числа - выдачи зп и аванса - в эти моменты всплеск активности - в это время нужно завозить товар. Исключение январь - там другая зависимость из-за каникул. В данных нет информации, но могу предположить что март - месяц женского праздника в сети будет спад, а к середине апреля и далее рост продаж из-за старта дачного сезони и майских выходных. В программе лояльности места по количеству проданного товара на покупателя распределены между Shop 19, Shop 28, Shop 0 и Shop 8. В Первых двух по программе лояльности продано товара больше чем без программы лояльности. Явным лидером по количеству чеков на человека является Shop 6 (в 6 раз больше чем в среднем по магазинам) - на этот магазин стоит обратить внимание.

Расчет затрат на программу лояльности вёлся следующим образом:
Мы предположили что затраты - это разница суммы продаж по прайсу с медианными ценами и с минимальными ценами. К этой сумме были прибавлены все подарки (товары 0 количеством в чеке) по цене медианного прайса, плюс заработная плата маркетолога за 3 месяца (100000 * 3=300000 рублей), плюс затраты на выпуск карт лояльности 2000 штук по цене 25 рублей. Из этой суммы была вычтена сумма продаж карт лояльности за все 3 месяца.
Затраты поделили на количество лояльных клиентов и получили затраты на привлечение одного клиента в программу лояльности порядка 1000 рублей.

Анализ ведется по таблице с 4061 чеку. Из нее выделено 1727 профиля покупателя. Когорты сформированы по дню первой покупки, в данных всего 75 лайфтаймов(дней жизни клента с момента первого чека).
На графике динамики привлечения покупателей видно, что максимальное количество привлеченных покупателей было накануне нового года в декабре, в январе и феврале привлеченных новых покупателей в разы меньше.
Клиенты пришедшие в магазин в день влюбленных "не наш профиль" и на день защитника отечества приходят только постоянные(не новые) клиенты, которые знают ассортимент и делают осознанные покупки.
Тепловая карта удержания клиентов имеет неоднородный характер, это може свидетельствовать о том, что в строительные магазины заходят крайне редко и нерегулярно.
Количество лояльных пользователей уменьшается от месяца к месяцу более чем в 2 раза каждый месяц - это свидетельствует об отказе клиентов оплачивать карту лояльности из-за того что выгода по ней может быть меньше чем затраты, однако также в этом вопросе нельзя исключать фактор сезонности. Есть вероятность того что с началом дачного сезона в апреле-марте покупок станет больше и клиентов программы лояльности тоже. Получается что затраты среднего клиента программы лояльности на карты привелегий за три месяца составили 589 рублей.
До учета выручки от карт лояльности накопленная выручка на человека LTV по программе лояльности была ниже чем клиента не участвующего в ней. При учете выручки от карт - лояльные клиенты дают выручку сети наших магазинов все равно ниже чем нелояльне. Накопленная выручка клиентов программы лояльности растет медленней чем выручка у обычных клиентов.
Программа лояльности не окупилась. В динамике LTV больше всего накапливалась накануне праздников(подарки) на новый год и день защитника отечества.
Средний чек по всей таблице примерно 358 рублей, по программе лояльности на 54% ниже, без программы - на 29% выше.
Медианный чек по всей таблице составляет 176 рублей, по программе лояльности на 44% ниже, без программы лояльности на 48% выше.
Медианный чек самый высокий в январе - люди сами совершают покупки в период новогодних праздников, есть версия что на подаренные деньги. Декабрьский медианный чек самый низкий - новогодние сувениры как правило недорогие. Февральский чет выше декабрьского но меньше январского.
Среднее количество уникальных пользователей за день: 27
Среднее количество уникальных пользователей за неделю: 140
Среднее количество уникальных пользователей за месяц: 514
В день привлекалось больше всего в январе, по неделям - максимум на 49-ю неделю (сезон новогодних распродаж). Помесячно количество лояльных клиентов постоянно сокращается.
Проверка статистических гипотез о равности среднего чека по программе лояльности и без по всей сети и в магазине 0 - не подтвердилась.
Итого: Программа лояльности снижает средний чек, удержание клиентов, количество товара в чеке и количество покупок на человека. Кроме того количество проданных карт лояльности от месяца к месяцу становится меньше. Также большое количество проданного товара по программе лояльности привело к остановке работы одного из магазинов.
Следовательно, программа лояльности не дала ожидаемого результата, только подсветила аудиторию (скорее всего профессионалы строительного бизнеса). Программу лояльности нужно либо отменить, либо пересмотреть ее условия.

7. Рекомендации и материалы для отдела маркетинга и коммерческого департамента

К плану работ

Использовать информацию рекомендаций для руководителей, подробно изучить работу маназина 19 и 5. Ориентация программы лояльности должна быть на профессионалов строительной отрасли, товары должны завозиться к 5 и 20 числу, у сезон спада продаж - увеличивать активность программы по стимулированию привлечения покупателей, перед новым годом стимулировать к большему чеку, после нового года по увеличению количества проданного товара.

По итогам исследования презентация в процессе подготовки, после получения замечаний будет приложена к проекту